using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Reflection;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using Spread.Common.DataStore;
using Spread.Common.DataStore.DataSourceEntities;
using Spread.Common.DataStore.Models;

namespace GrapeCity.IncomeStatement
{
    public partial class IncomeStatementControl : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Private variables
        private List<BusinessStat> _businessStats;
        private List<BusinessStat> _currentYearStats;
        private IncomeStatementDataSource _dataSource;
        #endregion

        #region ctor
        public IncomeStatementControl()
        {
            InitializeComponent();

            _dataSource = new DataService().IncomeStatementDataSource;
            _businessStats = _dataSource.GetAllBusinessStats();

            Load += IncomeStatementControl_Load;
        }
        #endregion

        #region Event handlers
        private void IncomeStatementControl_Load(object sender, System.EventArgs e)
        {
            fpSpread.ActiveSheet.OpenExcel(@"Template\IncomeStatement.xlsx", 0);

            SetSheetProperties(fpSpread.ActiveSheet);
            LoadBusinessStats(fpSpread.ActiveSheet, 4, DateTime.Now.Year);
        }

        private void ComboCell_EditingStopped(object sender, EventArgs e)
        {
            int year = Convert.ToInt32(fpSpread.ActiveSheet.Cells["YearCell"].Value);
            ClearContents(fpSpread.ActiveSheet, 9, 4, 11, 15);
            ClearContents(fpSpread.ActiveSheet, 14, 4, 15, 15);
            ClearContents(fpSpread.ActiveSheet, 20, 4, 25, 15);
            ClearContents(fpSpread.ActiveSheet, 30, 4, 32, 15);
            LoadBusinessStats(fpSpread.ActiveSheet, 4, year);
        }
        #endregion

        #region Private methods
        private void SetSheetProperties(SheetView sheet)
        {
            fpSpread.SuspendLayout();
            sheet.RowCount = 38;
            sheet.ColumnCount = 16;
            sheet.SheetName = "Income Statement";

            sheet.FpSpread.ButtonDrawMode = ButtonDrawModes.Always;
            sheet.Cells[9, 1].Tag = "Product";
            sheet.Cells[10, 1].Tag = "Services";
            sheet.Cells[11, 1].Tag = "Other";
            sheet.Cells[14, 1].Tag = "CostOfGoods";
            sheet.Cells[15, 1].Tag = "DirectCharges";
            sheet.Cells[20, 1].Tag = "PayrollCost";
            sheet.Cells[21, 1].Tag = "AdminstrativeCost";
            sheet.Cells[22, 1].Tag = "TravelCost";
            sheet.Cells[23, 1].Tag = "MarketingCost";
            sheet.Cells[24, 1].Tag = "OutsourcingCost";
            sheet.Cells[25, 1].Tag = "UtilityCost";
            sheet.Cells[30, 1].Tag = "InterestExpense";
            sheet.Cells[31, 1].Tag = "InterestIncome";
            sheet.Cells[32, 1].Tag = "DepreciationCost";

            sheet.Protect = false;
            sheet.Cells[8, 2, sheet.RowCount - 1, sheet.ColumnCount - 1].Locked = false;

            AddComboSelector(sheet, 1, 3);

            fpSpread.ResumeLayout(true);
        }

        // Add ComboBox cell for Year selector
        private void AddComboSelector(SheetView sheet, int row, int col)
        {
            ComboBoxCellType comboCell = new ComboBoxCellType
            {
                Editable = false,
                DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                Items = new string[] { (DateTime.Now.Year - 2).ToString(), (DateTime.Now.Year - 1).ToString(),
                    (DateTime.Now.Year.ToString())},
                StopEditingAfterDropDownItemSelected = true
            };
            comboCell.EditingStopped += ComboCell_EditingStopped;

            sheet.AddSpanCell(row, col, 1, 2);
            sheet.Cells[row, col].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[row, col].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[row, col].Font = new Font("Segoe UI", 18, FontStyle.Regular);
            sheet.Cells[row, col].CellType = comboCell;
            sheet.Cells[row, col].Value = DateTime.Now.Year.ToString();
            sheet.Cells[row, col].Tag = "YearCell";
            sheet.Cells[row, col].Locked = false;
        }

        // Load stats of selected year
        private void LoadBusinessStats(SheetView sheet, int col, int year)
        {
            DateTime date = DateTime.Now.Date;
            _currentYearStats = _businessStats.Where(stat => stat.Year == year).ToList();
            if (_currentYearStats.First().Year == date.Year)
                _currentYearStats = _currentYearStats.Where(stat => stat.Month <= date.Month).ToList();

            PropertyInfo[] revenueProperties = _businessStats[0].Revenue.GetType().GetProperties();
            PropertyInfo[] expenseProperties = _businessStats[0].Expense.GetType().GetProperties();
            List<string> revenuePropertyNames = revenueProperties.Select(prop => prop.Name).ToList();
            List<string> expensePropertyNames = expenseProperties.Select(prop => prop.Name).ToList();

            // Suspend layout to prevent update of layout after change in each cell
            sheet.AutoCalculation = false;
            fpSpread.SuspendLayout();

            foreach (var stat in _currentYearStats)
            {
                for (int i = 0; i < revenueProperties.Count(); ++i)
                {
                    sheet.Cells[sheet.Cells[revenuePropertyNames[i]].Row.Index, col + stat.Month - 1].Value =
                        revenueProperties[i].GetValue(stat.Revenue);
                }

                for (int i = 0; i < expenseProperties.Count(); ++i)
                {
                    if (expensePropertyNames[i] == "DepreciationCost" || expensePropertyNames[i] == "AmortizationCost")
                        continue;
                    sheet.Cells[sheet.Cells[expensePropertyNames[i]].Row.Index, col + stat.Month - 1].Value =
                        expenseProperties[i].GetValue(stat.Expense);
                }

                sheet.Cells[sheet.Cells["DepreciationCost"].Row.Index, col + stat.Month - 1].Value = stat.Expense.
                    DepreciationCost + stat.Expense.AmortizationCost;
            }

            // Resume layout after all values are set
            sheet.AutoCalculation = true;
            fpSpread.ResumeLayout(true);
        }

        private void ClearContents(SheetView sheet, int row, int col, int endRow, int endCol)
        {
            sheet.ClearRange(row, col, endRow - row + 1, endCol - col + 1, true);
        }
        #endregion
    }
}


//
//IncomeStatementControl.Designer.cs
//


namespace GrapeCity.IncomeStatement
{
    partial class IncomeStatementControl
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "";
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // IncomeStatementControl
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "IncomeStatementControl";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
    }
}
